package com.xxzb.pack0722.组装.dao;


import com.xxzb.bean.Emp;

import com.xxzb.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    //查询全部的员工信息，但是没有部门信息
    public List<Emp> find() {
        List<Emp> list = new ArrayList<>();
        String sql = "select * from gaofeng2207.emp where empstate=1";
        try {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                String hireDate = rs.getString(5);
                double sal = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptNo = rs.getInt(8);
                Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                list.add(emp);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    //根据部门编号获取emp表的全部信息
    public List<Emp> findByDeptNo(int deptNo) throws Exception {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from gaofeng2207.emp where deptNo=? and empstate=1";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, deptNo);
        rs = ps.executeQuery();
        while (rs.next()) {
            int empNo = rs.getInt(1);
            String ename = rs.getString(2);
            String job = rs.getString(3);
            int mgr = rs.getInt(4);
            String hireDate = rs.getString(5);
            double sal = rs.getDouble(6);
            double comm = rs.getDouble(7);
            //将查询处理的一行数据封装到一个对象中
            Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
            //将产生的对象存放到集合中
            list.add(emp);
        }
        return list;

    }

}
